# Copyright (c) 2025, hzmes and contributors
# For license information, please see license.txt

import frappe
from frappe import _

def execute(filters=None):
	# get_data要传参filters
	return get_columns(filters), get_data(filters)

def get_columns(filters):
	columns = [
		{"label": _("Project"),"fieldtype": "Data","fieldname": "project","options": "Project","width": 100,},
		{"label": _("Project Name"),"fieldtype": "Data","fieldname": "project_name","width": 300,},
		{"label": _("Date"),"fieldtype": "Date","fieldname": "date","width": 130,},
		{"label": _("Project Current Schedule"),"fieldtype": "Data","fieldname": "current_schedule","width": 130,},
		{"label": _("Budget"),"fieldtype": "Currency","fieldname": "budget","width": 100,},
		{"label": _("Cost"),"fieldtype": "Currency","fieldname": "cost","width": 120,},
		{"label": _("Project Manager"),"fieldtype": "Data","fieldname": "project_manager","width": 100,},
	]
	sql_expense = """ SELECT DISTINCT tb1.expense FROM
				(
				SELECT DISTINCT detail_expense_name as expense FROM `tabGL Voucher Allocation`
				UNION ALL
				SELECT DISTINCT expense_claim_type as expense FROM `tabExpense Allocation` 
				UNION ALL
				SELECT '材料费' as expense
				) tb1
	"""
	# 计算有哪些项目明细动态增加列
	expenses = frappe.db.sql(sql_expense, as_dict=True)
	if len(expenses) > 0:
		for ex in expenses:
			element = {"label": ex.expense,"fieldtype": "Data","fieldname": ex.expense,"width": 100,}
			columns.append(element)
	return columns

def get_data(filters):
	# 获取filters
	from_date, to_date = filters.get('from_date'), filters.get('to_date')
	# 先配置默认conditions再通过if判断是否增加条件项
	conditions = " AND 1=1 "
	total = []	# 默认空列表
	sql_total = f""" 
		    SELECT tb1.project, tb2.project_name, tb2.expected_start_date as date, tb2.custom_current_schedule as current_schedule, tb2.custom_estimated_development_cost as budget, SUM(tb1.amount) as cost, tb2.custom_pm_fullname as project_manager FROM
			(
			SELECT detail_project_number as project, detail_expense_name as expense, SUM(amount) as amount FROM `tabGL Voucher Allocation` GROUP BY detail_project_number, detail_expense_name
			UNION ALL
			SELECT project, expense_claim_type as expense, SUM(amount) as amount FROM `tabExpense Allocation` GROUP BY project, expense_claim_type
			UNION ALL
			SELECT tb1.project, '材料费' as expense, SUM(tb2.amount) as amount FROM `tabMiscellaneous Delivery` tb1 JOIN `tabDelivery Items` tb2 on tb1.name=tb2.parent WHERE 1=1 GROUP BY project
			) tb1 LEFT JOIN `tabProject` tb2 on tb1.project = tb2.name
			WHERE tb2.expected_start_date>='{from_date}' AND tb2.expected_start_date<='{to_date}' {conditions}
			GROUP BY tb1.project
	 """
	# 计算每个项目的合计数
	total = frappe.db.sql(sql_total, as_dict=True)
	if len(total) > 0:

		sql_expense = """ SELECT DISTINCT tb1.expense FROM
				(
				SELECT DISTINCT detail_expense_name as expense FROM `tabGL Voucher Allocation`
				UNION ALL
				SELECT DISTINCT expense_claim_type as expense FROM `tabExpense Allocation` 
				UNION ALL
				SELECT '材料费' as expense
				) tb1
		"""
		# 计算有哪些项目明细
		expenses = frappe.db.sql(sql_expense, as_dict=True)
		if len(expenses) > 0:
			# 往total元素中增加鍵值对: {"项目明细": 0}
			for i in total:
				for ex in expenses:
					i[ex.expense] = 0	# 默认值=0

			sql_detail = """ SELECT detail_project_number as project, detail_expense_name as expense, SUM(amount) as amount FROM `tabGL Voucher Allocation` GROUP BY detail_project_number, detail_expense_name
				UNION ALL
				SELECT project, expense_claim_type as expense, SUM(amount) as amount FROM `tabExpense Allocation` GROUP BY project, expense_claim_type
				UNION ALL
				SELECT tb1.project, '材料费' as expense, SUM(tb2.amount) as amount FROM `tabMiscellaneous Delivery` tb1 JOIN `tabDelivery Items` tb2 on tb1.name=tb2.parent WHERE 1=1 GROUP BY project
			"""

			# 计算全部项目明细
			details = frappe.db.sql(sql_detail, as_dict=True)
			if len(details) > 0:
				for item in details:
					for pro in total:
						if pro.get('project') == item.project:
							pro[item.expense] = item.amount

	return total


